const pool = require('./db');

// 初始化数据库表结构
const initDatabase = async () => {
  const connection = await pool.getConnection();
  try {
    console.log('开始初始化数据库表结构...');
    
    // 创建歌手表 (songers)
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS songers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(191) NOT NULL UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('✓ songers表创建完成');
    
    // 创建歌曲表 (songs) - 包含与歌手的关联
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS songs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(191) NOT NULL,
        title VARCHAR(191) NOT NULL,
        artist VARCHAR(191) NOT NULL,
        artist_id INT,
        lyrics TEXT,
        song_url VARCHAR(500) NOT NULL,
        cover_url VARCHAR(500),
        duration INT DEFAULT 0,
        play_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (artist_id) REFERENCES songers(id) ON DELETE SET NULL,
        INDEX idx_artist_id (artist_id),
        INDEX idx_name (name(191)),
        INDEX idx_artist (artist(191))
      )
    `);
    console.log('✓ songs表创建完成');
    
    // 创建歌单表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS playlists (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        cover_url VARCHAR(500),
        is_public BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('✓ playlists表创建完成');
    
    // 创建歌单-歌曲关联表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS playlist_songs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        playlist_id INT NOT NULL,
        song_id INT NOT NULL,
        sort_order INT DEFAULT 0,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY unique_playlist_song (playlist_id, song_id)
      )
    `);
    console.log('✓ playlist_songs表创建完成');
    
    // 创建用户收藏表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_favorites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(100) NOT NULL,
        song_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_song (user_id, song_id)
      )
    `);
    console.log('✓ user_favorites表创建完成');
    
    // 创建播放历史表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS play_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(100) NOT NULL,
        song_id INT NOT NULL,
        played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE
      )
    `);
    console.log('✓ play_history表创建完成');
    
    console.log('🎉 数据库表结构初始化完成！');
    
    // 检查现有数据
    const [songCount] = await connection.execute('SELECT COUNT(*) as count FROM songs');
    const [singerCount] = await connection.execute('SELECT COUNT(*) as count FROM songers');
    
    console.log(`📊 当前数据统计:`);
    console.log(`   - 歌曲数量: ${songCount[0].count}`);
    console.log(`   - 歌手数量: ${singerCount[0].count}`);
    
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
    throw error;
  } finally {
    connection.release();
  }
};

// 如果直接运行此文件，则执行初始化
if (require.main === module) {
  initDatabase()
    .then(() => {
      console.log('数据库初始化完成，程序退出');
      process.exit(0);
    })
    .catch((error) => {
      console.error('数据库初始化失败:', error);
      process.exit(1);
    });
}

module.exports = { initDatabase };